Additional Features Provided for Querying a Database

This topic describes the additional features provided in Process Platform for querying a database.

Features

parameterlist - This feature allows the request to contain multiple values for a single parameter. This is useful in case of dynamic IN clause queries. Consider the following implementation which queries Employees Tablewith a single parameter:

 <implementation type="DBSQL">
        <constructor language="DBSQL">
             <query>SELECT * FROM Employees WHERE EmployeeID = :EmployeeID</query>
             <parameterlist>
            <parameters>
            <EmployeeID dd="Employees.EmployeeID" dt="i4"/>
            </parameters>
            </parameterlist>
        </constructor>
    </implementation>
        <SOAP:Body>

The request for such a query will be:

 <soap:envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
 <GetEmployee xmlns="http://schemas.cordys.com/1.0/demo/Eastwind">
                <parameters>
                    <EmployeeID>1</EmployeeID>
                </parameters>
                <parameters>
                    <EmployeeID>2</EmployeeID>
                </parameters>
            </GetEmployee><soap:envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
        <SOAP:Body>

This feature offers the following advantages:

Network Roundtrips for each execution to the bus request can be avoided
Lessens the executions and provides results to the user at an optimal time

Dynamic IN Clause- Consider the query below that has an IN clause querying for the Employees table:
```
<query>SELECT * FROM Employees WHERE EmployeeID IN (:EmployeeID)</query>
```
The above query can be executed for multiple values of EmployeeID through the OLEDB Service Container. The number of values in this case are dynamic and are programmatically generated. In such cases, where the IN clause is not used, users have to call a Web service operation for each of the obtained values that results in one request for each execution and more number of network roundtrips. Also, the execution time increases and more time is taken to return the result. This problem can be avoided by passing multiple values in the <parameter> tag as follows:
```
<parameters>
    <EmployeeID>
        <value>1</value>
        <value>2</value>
        <value>3</value>
        <value>4</value>
    </EmployeeID>
</parameters>
```
Note:The request can be fired successfully without containing the <parameters> tags.In the above case, the query is executed only once and the result is returned for all the four values in a single execution. This can also be used for more than one IN clause as shown in the query below:
```
<query>SELECT * FROM Employees WHERE EmployeeID IN (:EmployeeID) AND City IN (:City)</query>
```
The parameters are passed for the above given query as follows:
```
<parameters>
    <EmployeeID>
        <value>1</value>
        <value>2</value>
        <value>3</value>
    </EmployeeID>
    <City>
        <value>Seattle</value>
        <value>Tacoma</value>
    </City>
</parameters>
```
Parameters with attribute information - The implementation created using Method Generatorcontains the parameter information for each parameter sent as an attribute to the back-end. Hence, this information need not be supplied by the developer for each request. The information about the parameter is generated as follows:
```
<implementation type="DBSQL">
    <constructor language="DBSQL">
        <query>SELECT * FROM Employees WHERE EmployeeID BETWEEN :fromEmployeeID AND :toEmployeeID</query>
        <parameters>
            <fromEmployeeID dd="Employees.EmployeeID"/>
            <toEmployeeID dd="Employees.EmployeeID"/>
        </parameters>
    </constructor>
</implementation>
```
Note: The implementation type is DBSQL, and the constructor language is DBSQL (for tables) and DBRPC (for stored procedures).
REFCURSOR parameter support for Oracle Databases -REFCURSOR is cursor type that is used for returning a set of rows (Result Set) from a table. Oracle Provider for OLE DB (OraOLEDB) allows executing a Stored Procedure with an argument of REFCURSOR type or a Stored Function that returns a REFCURSOR. The data returned is up to one rowset per Stored Procedure or a Function Call. If the procedure has one or more arguments of REFCURSOR type, the provider binds these arguments appropriately and returns a row set for the first argument of REFCURSOR type.The user should send all other IN and OUT parameters, but should not send parameters of type REFCURSOR. The Stored Procedure returning result set must be in a package. The SOAP method definition should contain the package name along with the procedure in capital letters. For example, <query>TESTPACKAGE.TESTPROCEDURE(:fromDeptNo,:toDeptNo)</query>
Note:
Only OLEDB provider by oracle (OraOLEDB.Oracle) supports REFCURSOR

User has to set PLSQLRSet parameter in the registry path HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\OLEDB to 1
For example, consider the following package definition in Oracle Stored Procedure, which refers to another package:

CREATE OR REPLACE PACKAGE TestPackage AS
    TYPE empcur IS REF CURSOR;
    PROCEDURE TestProcedure(p_cursor OUT empcur,fromDeptNo IN number,toDeptNo IN number);
END TestPackage ;

The implementation of the package that is referred by the REFCURSOR:

 CREATE OR REPLACE PACKAGE BODY TestPackage AS
    PROCEDURE TestProcedure(p_cursor OUT empcur,fromDeptNo IN number,toDeptNo IN number) IS
BEGIN
        OPEN p_cursor FOR
        SELECT *
FROM DEPT where DEPTNO between fromDeptNo and toDeptNo ;
    END TestProcedure;
END TestPackage ;

The implementation for the above procedure in OraOLEDB:

<implementation type="DBSQL">
    <constructor language="DBRPC">
        <query>TestPackage.TestProcedure(:fromDeptNo,:toDeptNo)</query>
        <parameters/>
    </constructor>
</implementation>

The user request sent to the back end is as follows:

 <soap:envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<SOAP:Body>
<OracleCursor xmlns="http://schemas.cordys.com/CustOrderHist">
            <fromDeptNo>1</fromDeptNo>
            <toDeptNo>30</toDeptNo>
        </OracleCursor>
<soap:envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<SOAP:Body>

As mentioned above, the request does not supply the REFCURSOR property, but the records are received from the back end by handling the REFCURSOR internally:

<data>
    <OracleCursorResponse xmlns="http://schemas.cordys.com/CustOrderHist">
        <tuple>
            <old>
                <TestPackage.TestProcedure/>
                <result>
                    <tuple>
                        <DEPTNO>10</DEPTNO>
                        <DNAME>ACCOUNTING</DNAME>
                        <LOC>NEW YORK</LOC>
                    </tuple>
                    <tuple>
                        <DEPTNO>20</DEPTNO>
                        <DNAME>RESEARCH</DNAME>
                        <LOC>DALLAS</LOC>
                    </tuple>
                    <tuple>
                        <DEPTNO>30</DEPTNO>
                        <DNAME>SALES</DNAME>
                        <LOC>CHICAGO</LOC>
                    </tuple>
                </result>
            </old>
        </tuple>
    </OracleCursorResponse>
</data>

Support for table names with spaces- Tables with spaces in the table names are also supported in Cordys.
- Now, BLOB fields can also be inserted in Oracle databases.
- BLOB type fields are also supported in Informix.
Performance improvement with no-reply option - When working with bulk inserts, the performance is slowed down due to the presence of auto-generated fields in the response or due to responses being generated after every read/insert. The performance of insert operation can be improved by using the reply option. By setting reply='no' attribute for the 'Update' tag in the implementation, you can avoid auto-generated responses and improve the connector performance.

pp108 : Additional Features Provided for Querying a Database

Additional Features Provided for Querying a Database

Features